package com.roc.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.DecimalFormat;
import java.text.NumberFormat;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang3.ObjectUtils;
import org.apache.commons.lang3.math.NumberUtils;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Color;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.PrintSetup;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.roc.pojo.Operators;
import com.roc.pojo.ProfitShare;

public class ExcelUtil {
	static final Logger logger = Logger.getLogger(ExcelUtil.class);
	private static final NumberFormat doubleFormat = new DecimalFormat("#.00");
	private static final NumberFormat integerFormat = DecimalFormat
			.getIntegerInstance();
	
	public static StringBuffer errorMessage;  //解析Excel文件时遇到的错误信息，用于存储错误单元格的位置信息和错误原因

	public static void main(String[] args) {
		/**
		 * 952700010270000-952700010299499
		 */

		Long startSearial = 23L;
		Long startPsamCard = 952700010256900l;
		int totalNum = 100;
		String phone = "18612123456";
		String orgName = "1";
		try {
			pro_excel(startSearial, startPsamCard, totalNum, phone, orgName);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public static boolean pro_excel(long startSerial, long startPsamCardNo,
			int num, String phone, String orgName) throws Exception {
		HSSFWorkbook wb = new HSSFWorkbook();

		HSSFSheet sheet = wb.createSheet("PSAM卡号");

		HSSFRow row = sheet.createRow(0);

		//
		HSSFCell cell = row.createCell((short) 0);

		cell.setCellValue("终端序列号");
		cell = row.createCell((short) 1);

		cell.setCellValue("PSAM卡号");
		cell = row.createCell((short) 2);

		cell.setCellValue("手机号");
		int j = 0;
		for (int i = 0; i < num; i++) {

			row = sheet.createRow(i + 1);
			cell = row.createCell((short) 0);

			cell.setCellValue(String.valueOf((startSerial + i)) + "");

			cell = row.createCell((short) 1);

			cell.setCellValue((startPsamCardNo + i) + "");
			cell = row.createCell((short) 2);

			cell.setCellValue(phone);
			j++;
		}

		// 最后一步，将文件存到指定位置
		String testPath = "d://PsamCard";
		String date = Constance.formatDate("yyyyMMdd");
		// String naestPath+merchant+"_"+date+".xls";
		File file = new File(testPath);
		if (!file.exists()) {
			logger.info("#############" + testPath
					+ "is not exists and create it############");
			file.mkdirs();
		}
		String path = testPath + "/" + date;

		File files = new File(path);
		if (!files.exists()) {
			logger.info("#############" + path
					+ "is not exists and create it############");
			files.mkdirs();
		}

		try {

			logger.info("#############start to create [psamCard_" + date
					+ ".xlsx]############");
			FileOutputStream fout = new FileOutputStream(path + "/"
					+ "psamCard" + "_" + date + "_" + orgName + ".xlsx");

			wb.write(fout);
			fout.close();

			logger.info("#############" + "psamCard" + "_" + date
					+ ".xlsx proccess finished. total:[" + j
					+ "] is ok############");
		} catch (Exception e) {
			logger.info("#######.生成Excel失败#########", e);
			e.printStackTrace();

		}

		return true;
	}

	/**
	 * 分润确认单
	 * 
	 * @param merchant
	 * @param list
	 * @param remotePath
	 * @param date
	 * @return
	 * @throws Exception
	 */
	public static SXSSFWorkbook excelExport(Operators merchant,
			List<ProfitShare> list,String date)
			throws Exception {
		SXSSFWorkbook wb = new SXSSFWorkbook(1000);
		try {

			// 第一行
			CellStyle style = initStyle(wb, CellStyle.ALIGN_CENTER, "微软雅黑", 16);
			style.setVerticalAlignment(CellStyle.ALIGN_CENTER);
			// //第二行
			CellStyle style1 = initStyle(wb, CellStyle.VERTICAL_TOP, "宋体", 11);
			style1.setWrapText(true);

			// 第三行
			CellStyle style2 = initStyle(wb, CellStyle.ALIGN_CENTER, "微软雅黑", 12);
			// style2.se
			// 第四行
			CellStyle style3 = initStyle(wb, CellStyle.ALIGN_CENTER_SELECTION,
					"宋体", 10);
			// style3.setIndention((short) 2);
			CellStyle style_r = initStyle(wb, CellStyle.ALIGN_RIGHT, "宋体", 10);
			// style_r.setIndention((short) 2);
			CellStyle style_l = initStyle(wb, CellStyle.ALIGN_LEFT, "宋体", 10);
			// style_l.setIndention((short) 2);
			// 小计样式
			CellStyle cs = initStyle(wb, CellStyle.ALIGN_RIGHT, "宋体", 10);

			String merchantName = merchant.getRealName();
			String bankName = merchant.getBankDeposit();
			String bankAcctNo = merchant.getBankAcctNo();
			String bankAcctName = merchant.getBankAcctName();

			String nowDate = Constance.currTime();
			String year = date.substring(0, 4);
			String month = date.substring(4, 6);

			Sheet sheet = wb.createSheet(year + "年" + month + "月POS平台分润确认函");
			int totalColums = 9;
			int totalRows = 0;
			for (int i = 0; i < totalColums; i++) {
				// column width is set in units of 1/256th of a character width
				if (i == 0 || i == 4) {
					sheet.setColumnWidth(i, 256 * 9);
				} else if (i == 1 || i == 5 || i == 7 || i == 8) {
					sheet.setColumnWidth(i, 256 * 10);
				} else if (i == 3) {
					sheet.setColumnWidth(i, 256 * 15);
				} else {
					sheet.setColumnWidth(i, 256 * 13);
				}

			}
			Row row0 = sheet.createRow(totalRows++);
			row0.setHeightInPoints(30);
			Cell cTitle = row0.createCell(0);

			cTitle.setCellValue("POS平台结算款确认函");
			cTitle.setCellStyle(style);
			// 处理空行
			dealCell(totalColums, row0, style);

			sheet
					.addMergedRegion(new CellRangeAddress(0, 0, 0,
							totalColums - 1));
			Row rowInfo = sheet.createRow(totalRows++);
			// row1.setHeightInPoints(125);
			CellStyle infoStyle = initStyle_nobord(wb, CellStyle.VERTICAL_TOP,
					"宋体", 9);
			CellStyle infoStyle_u = initStyle_uline(wb, CellStyle.VERTICAL_TOP,
					"宋体", 9);
			CellStyle infoStyle_left = initStyle_left(wb,
					CellStyle.VERTICAL_TOP, "宋体", 9);
			CellStyle infoStyle_right = initStyle_right(wb,
					CellStyle.VERTICAL_TOP, "宋体", 9);
			CellStyle infoStyle_right_u = initStyle_right_u(wb,
					CellStyle.VERTICAL_TOP, "宋体", 9);

			Cell con = rowInfo.createCell(0);

			// con.setCellValue("    品牌名称："+ob.getOrg_name()+" \n    结算收款信息：账户名："+bankAcctName+"  \n                  开户名："+bankName+"  \n                  账号："+bankAcctNo+" \n    "+year+" 年 __"+month+"__ 月应结算总金额  ：小写_______________元\n                                     大写________________元                     签字并盖章：                                                        \n ");
			con.setCellValue("品牌名称：");
			con.setCellStyle(infoStyle_left);
			con = rowInfo.createCell(1);
			con.setCellValue("POS收单");
			con.setCellStyle(infoStyle_u);
			// sheet.addMergedRegion(new CellRangeAddress(1,1,0,1));
			con = rowInfo.createCell(2);
			con.setCellValue("代理商姓名：");
			con.setCellStyle(infoStyle);
			con = rowInfo.createCell(3);
			con.setCellValue(merchantName);
			con.setCellStyle(infoStyle_u);
			dealCell(4, totalColums - 1, rowInfo, infoStyle);
			con = rowInfo.createCell(totalColums - 1);
			con.setCellValue("");
			con.setCellStyle(infoStyle_right);

			sheet
					.addMergedRegion(new CellRangeAddress(1, 1, 4,
							totalColums - 1));

			rowInfo = sheet.createRow(totalRows++);
			con = rowInfo.createCell(0);

			con.setCellValue("收款信息：");
			con.setCellStyle(infoStyle_left);
			con = rowInfo.createCell(1);
			con.setCellValue("账户名：");
			con.setCellStyle(infoStyle);
			// sheet.addMergedRegion(new CellRangeAddress(1,1,0,1));
			con = rowInfo.createCell(2);
			con.setCellValue(bankAcctName);
			con.setCellStyle(infoStyle_u);
			dealCell(3, 5, rowInfo, infoStyle_u);
			sheet.addMergedRegion(new CellRangeAddress(2, 2, 2, 4));
			dealCell(5, totalColums - 1, rowInfo, infoStyle);
			con = rowInfo.createCell(totalColums - 1);
			con.setCellValue("");
			con.setCellStyle(infoStyle_right);

			rowInfo = sheet.createRow(totalRows++);
			con = rowInfo.createCell(0);

			con.setCellValue("");
			con.setCellStyle(infoStyle_left);
			con = rowInfo.createCell(1);
			con.setCellValue("开户行：");
			con.setCellStyle(infoStyle);
			// sheet.addMergedRegion(new CellRangeAddress(1,1,0,1));
			con = rowInfo.createCell(2);
			con.setCellValue(bankName);
			con.setCellStyle(infoStyle_u);
			dealCell(3, 5, rowInfo, infoStyle_u);
			sheet.addMergedRegion(new CellRangeAddress(3, 3, 2, 4));
			dealCell(5, totalColums - 1, rowInfo, infoStyle);
			con = rowInfo.createCell(totalColums - 1);
			con.setCellValue("");
			con.setCellStyle(infoStyle_right);

			rowInfo = sheet.createRow(totalRows++);
			con = rowInfo.createCell(0);

			con.setCellValue("");
			con.setCellStyle(infoStyle_left);
			con = rowInfo.createCell(1);
			con.setCellValue("账号：");
			con.setCellStyle(infoStyle);
			// sheet.addMergedRegion(new CellRangeAddress(1,1,0,1));
			con = rowInfo.createCell(2);
			con.setCellValue(bankAcctNo);
			con.setCellStyle(infoStyle_u);
			dealCell(3, 5, rowInfo, infoStyle_u);
			sheet.addMergedRegion(new CellRangeAddress(4, 4, 2, 4));
			dealCell(5, totalColums - 1, rowInfo, infoStyle);
			con = rowInfo.createCell(totalColums - 1);
			con.setCellValue("");
			con.setCellStyle(infoStyle_right);

			rowInfo = sheet.createRow(totalRows++);
			con = rowInfo.createCell(0);

			con.setCellValue(year + "年_" + month + "_月应结算总金额");
			con.setCellStyle(infoStyle_left);
			con = rowInfo.createCell(1);
			con.setCellValue("");
			con.setCellStyle(infoStyle);
			sheet.addMergedRegion(new CellRangeAddress(5, 5, 0, 1));
			con = rowInfo.createCell(2);
			con.setCellValue("人民币小写：");
			con.setCellStyle(infoStyle);
			Cell conAmt = rowInfo.createCell(3);
			conAmt.setCellValue("");
			conAmt.setCellStyle(infoStyle_u);
			con = rowInfo.createCell(4);
			con.setCellValue("");
			con.setCellStyle(infoStyle_u);
			sheet.addMergedRegion(new CellRangeAddress(5, 5, 3, 4));
			con = rowInfo.createCell(5);
			con.setCellValue("确认签字：");
			con.setCellStyle(infoStyle);
			dealCell(6, totalColums - 1, rowInfo, infoStyle);
			con = rowInfo.createCell(totalColums - 1);
			con.setCellValue("");
			con.setCellStyle(infoStyle_right);

			rowInfo = sheet.createRow(totalRows++);
			con = rowInfo.createCell(0);

			con.setCellValue("");
			con.setCellStyle(infoStyle_left);
			con = rowInfo.createCell(1);
			con.setCellValue("");
			con.setCellStyle(infoStyle);

			con = rowInfo.createCell(2);
			con.setCellValue("人民币大写：");
			con.setCellStyle(infoStyle);
			Cell conAmtBig = rowInfo.createCell(3);
			conAmtBig.setCellValue("");
			conAmtBig.setCellStyle(infoStyle_u);
			con = rowInfo.createCell(4);
			con.setCellValue("");
			con.setCellStyle(infoStyle_u);
			sheet.addMergedRegion(new CellRangeAddress(6, 6, 3, 4));
			con = rowInfo.createCell(5);
			con.setCellValue("盖    章：");
			con.setCellStyle(infoStyle);
			dealCell(6, totalColums - 1, rowInfo, infoStyle);
			con = rowInfo.createCell(totalColums - 1);
			con.setCellValue("");
			con.setCellStyle(infoStyle_right);

			//

			rowInfo = sheet.createRow(totalRows++);
			con = rowInfo.createCell(0);
			con.setCellValue("");
			con.setCellStyle(infoStyle_left);
			dealCell(1, totalColums - 1, rowInfo, infoStyle);
			con = rowInfo.createCell(totalColums - 1);
			con.setCellValue("");
			con.setCellStyle(infoStyle_right);
			// 汇总标题
			Row row_hun = sheet.createRow(totalRows++);

			Cell c_conHun = row_hun.createCell(0);

			c_conHun.setCellValue("   " + year + " 年  __" + month
					+ "__ 月POS收单平台业务结算汇总表");
			c_conHun.setCellStyle(style2);
			dealCell(totalColums, row_hun, style2);

			sheet
					.addMergedRegion(new CellRangeAddress(8, 8, 0,
							totalColums - 1));

			// 正常交易标题
			Row row_zheng = sheet.createRow(totalRows++);
			// fillCell("国银通代理商综合分润明细表", 0, row0);
			Cell c_zheng = row_zheng.createCell(0);

			c_zheng.setCellValue("   正常交易数据   ");
			c_zheng.setCellStyle(style2);
			dealCell(totalColums, row_zheng, style2);

			sheet
					.addMergedRegion(new CellRangeAddress(9, 9, 0,
							totalColums - 1));

			// /正常交易数据
			Row row2 = sheet.createRow(totalRows++);
			String[] titls = { "序号", "业务类型", "笔数", "交易金额(元)","手续费(元)","分润金额(元)", };
			int j = 0;
			int b = 0;
			for (int k = 0; k < titls.length; k++) {
				Cell c = null;

				if (k == 0 || k == 1 || k == 2) {
					c = row2.createCell(k);
					c.setCellValue(titls[k]);
					c.setCellStyle(style2);
				}
				if (k == 3) {
					c = row2.createCell(k);
					c.setCellValue(titls[k]);
					c.setCellStyle(style2);
					c = row2.createCell(k + 1);
					c.setCellValue("");
					c.setCellStyle(style2);
					sheet.addMergedRegion(new CellRangeAddress(10, 10, 3, 4));
					j = j + 1;
				}
				if (k == 4) {
					c = row2.createCell(k + 1);
					c.setCellValue(titls[k]);

					c.setCellStyle(style2);
					c = row2.createCell(k + 2);
					c.setCellValue("");
					c.setCellStyle(style2);
					sheet.addMergedRegion(new CellRangeAddress(10, 10, 5, 6));
					j = j + 2;
				}
				if (k == 5) {
					c = row2.createCell(k + 2);
					c.setCellValue(titls[k]);

					c.setCellStyle(style2);
					c = row2.createCell(k + 3);
					c.setCellValue("");
					c.setCellStyle(style2);
					sheet.addMergedRegion(new CellRangeAddress(10, 10, 7, 8));

				}

				// b=b+2;
			}
			double sumAmt = 0.0;
			double sumProfit = 0.0;
			double sumMerFree = 0.0;
			long count = 0;
			int g = 0;
			// totalRows++;
			for (int i = 0; i < list.size(); i++) {
				// Object[] cc = content.get(i);
				ProfitShare pro = list.get(i);

				Row row = sheet.createRow(i + totalRows);
				Cell c = row.createCell(g);

				c.setCellValue(String.valueOf(i + 1));
				c.setCellStyle(style3);
				// sheet.addMergedRegion(new CellRangeAddress(i+5,i+5,0,1));
				Cell c1 = row.createCell(g + 1);
				c1.setCellValue(pro.getMccTypeName());
				c1.setCellStyle(style_l);
				// sheet.addMergedRegion(new CellRangeAddress(i+5,i+5,2,3));
				Cell c2 = row.createCell(g + 2);
				c2.setCellValue(pro.getCount1());
				c2.setCellStyle(style_r);

				Cell c3 = row.createCell(g + 3);
				c3.setCellValue(pro.getAmount());
				c3.setCellStyle(style_r);
				c3 = row.createCell(g + 4);
				c3.setCellValue("");
				c3.setCellStyle(style_r);
				sheet.addMergedRegion(new CellRangeAddress(i + totalRows, i
						+ totalRows, 3, 4));

				Cell c4 = row.createCell(g + 5);
				c4.setCellValue(pro.getMerfee());
				c4.setCellStyle(style_r);
				c4 = row.createCell(g + 6);
				c4.setCellValue("");
				c4.setCellStyle(style_r);
				sheet.addMergedRegion(new CellRangeAddress(i + totalRows, i
						+ totalRows, 5, 6));

				Cell c5 = row.createCell(g + 7);
				c5.setCellValue(pro.getFenrunAmt().toString());
				c5.setCellStyle(style_r);
				c5 = row.createCell(g + 8);
				c5.setCellValue("");
				c5.setCellStyle(style_r);
				sheet.addMergedRegion(new CellRangeAddress(i + totalRows, i
						+ totalRows, 7, 8));

				count += NumberUtils.toInt(ObjectUtils
						.toString(pro.getCount1()));
				sumAmt += NumberUtils.toDouble(ObjectUtils.toString(pro
						.getAmount()));
				sumProfit += NumberUtils.toDouble(ObjectUtils.toString(pro
						.getFenrunAmt()));
				sumMerFree += NumberUtils.toDouble(ObjectUtils.toString(pro
						.getMerfee()));

			}

			totalRows = list.size() + totalRows;

			Row rowLast = sheet.createRow(totalRows);

			Cell c_last = rowLast.createCell(0);
			c_last.setCellValue("小计 :");
			c_last.setCellStyle(cs);
			dealCell(4, rowLast, cs);
			sheet.addMergedRegion(new CellRangeAddress(totalRows, totalRows, 0,
					1));

			Cell clast1 = rowLast.createCell(2);
			clast1.setCellValue(count);
			clast1.setCellStyle(cs);
			// sheet.addMergedRegion(new CellRangeAddress(content.size() +5,
			// content.size() +5, 6,7));

			Cell clast2 = rowLast.createCell(3);
			clast2.setCellValue(format(sumAmt, Double.class));
			clast2.setCellStyle(cs);
			clast2 = rowLast.createCell(4);
			clast2.setCellValue("");
			clast2.setCellStyle(cs);
			sheet.addMergedRegion(new CellRangeAddress(totalRows, totalRows, 3,
					4));

			Cell clast3 = rowLast.createCell(5);
			clast3.setCellValue(format(sumMerFree, Double.class));
			clast3.setCellStyle(cs);
			clast3 = rowLast.createCell(6);
			clast3.setCellValue("");
			clast3.setCellStyle(cs);
			sheet.addMergedRegion(new CellRangeAddress(totalRows, totalRows, 5,
					6));

			Cell clast4 = rowLast.createCell(7);
			clast4.setCellValue(format(sumProfit, Double.class));
			clast4.setCellStyle(cs);
			clast4 = rowLast.createCell(8);
			clast4.setCellValue("");
			clast4.setCellStyle(cs);
			sheet.addMergedRegion(new CellRangeAddress(totalRows, totalRows, 7,
					8));

			totalRows++;
			rowInfo = sheet.createRow(totalRows);
			con = rowInfo.createCell(0);
			con.setCellValue("");
			con.setCellStyle(infoStyle_left);
			dealCell(1, totalColums - 1, rowInfo, infoStyle);
			con = rowInfo.createCell(totalColums - 1);
			con.setCellValue("");
			con.setCellStyle(infoStyle_right);
			totalRows++;
			// 强扣数据标题
			Row row_qiang = sheet.createRow(totalRows);
			// fillCell("国银通代理商综合分润明细表", 0, row0);
			Cell c_conqiang = row_qiang.createCell(0);

			c_conqiang.setCellValue("   强扣数据   ");
			c_conqiang.setCellStyle(style2);
			dealCell(totalColums, row_qiang, style2);
			sheet.addMergedRegion(new CellRangeAddress(totalRows, totalRows, 0,
					totalColums - 1));
			totalRows++;
			// 强扣数据
			Row qiangLast = sheet.createRow(totalRows);
			String[] qiangKouMin = { "交易类型", "交易日期", "交易流水", "商户号", "终端号",
					"交易卡号", "交易金额", "强扣金额", "强扣日期" };
			double qkAmt = 0;
			int y = 0;
			int x = 0;
			for (int k = 0; k < qiangKouMin.length; k++) {
				Cell c = qiangLast.createCell(x);
				c.setCellValue(qiangKouMin[k]);

				c.setCellStyle(style2);
				// sheet.addMergedRegion(new CellRangeAddress(content.size()
				// +8,content.size() +8,y,y+1));

				// y=y+2;
				x = x + 1;
			}
			totalRows++;
			Row qiangLast1 = sheet.createRow(totalRows);
			String[] qiangKouMin1 = { " ", " ", " ", " ", " ", " ", " ", " ",
					" " };
			int yy = 0;
			int xx = 0;
			for (int k = 0; k < qiangKouMin1.length; k++) {
				Cell c = qiangLast1.createCell(xx);
				c.setCellValue(qiangKouMin1[k]);

				c.setCellStyle(style3);
				// sheet.addMergedRegion(new CellRangeAddress(content.size()
				// +9,content.size() +9,yy,yy+1));

				// yy=yy+2;
				xx++;
			}
			totalRows++;
			Row rowLastend = sheet.createRow(totalRows);
			/*
			 * Cell last = rowLast.createCell(g); fillCell("小计", 2, rowLast);
			 * fillCell(count, 3, rowLast); fillCell(format(sumAmt,
			 * Double.class), 4, rowLast); fillCell(format(sumProfit,
			 * Double.class), 5, rowLast);
			 */

			Cell c_lastend = rowLastend.createCell(0);
			c_lastend.setCellValue("小计 :");
			c_lastend.setCellStyle(cs);
			dealCell(totalColums - 1, rowLastend, cs);
			sheet.addMergedRegion(new CellRangeAddress(totalRows, totalRows, 0,
					totalColums - 2));

			Cell c_lastend1 = rowLastend.createCell(totalColums - 1);
			c_lastend1.setCellValue("");
			c_lastend1.setCellStyle(style3);
			totalRows++;
			// /倒数 2
			Row rowlast_1 = sheet.createRow(totalRows);
			// fillCell("国银通代理商综合分润明细表", 0, row0);
			rowlast_1.setHeightInPoints(25);
			Cell c_cons = rowlast_1.createCell(0);
			c_cons.setCellValue("本月结算款=正常结算资金：");
			c_cons.setCellStyle(infoStyle_left);
			c_cons = rowlast_1.createCell(1);
			c_cons.setCellValue("");
			c_cons.setCellStyle(infoStyle);
			sheet.addMergedRegion(new CellRangeAddress(totalRows, totalRows, 0,
					1));
			c_cons = rowlast_1.createCell(2);
			c_cons.setCellValue(AmtUtil.amtFormat(sumProfit));
			c_cons.setCellStyle(infoStyle_u);
			c_cons = rowlast_1.createCell(3);
			c_cons.setCellValue("元-本月强扣资金：");
			c_cons.setCellStyle(infoStyle);
			c_cons = rowlast_1.createCell(4);
			c_cons.setCellValue(AmtUtil.amtFormat(qkAmt));
			c_cons.setCellStyle(infoStyle_u);
			c_cons = rowlast_1.createCell(5);
			c_cons.setCellValue("元=实际应结算总金额：");
			c_cons.setCellStyle(infoStyle);
			c_cons = rowlast_1.createCell(6);
			c_cons.setCellValue(AmtUtil.amtFormat(sumProfit - qkAmt) + " 元");
			c_cons.setCellStyle(infoStyle_u);
			c_cons = rowlast_1.createCell(7);
			c_cons.setCellValue("");
			c_cons.setCellStyle(infoStyle);
			c_cons = rowlast_1.createCell(8);
			c_cons.setCellValue("");
			c_cons.setCellStyle(infoStyle_right);

			conAmt.setCellValue(AmtUtil.amtFormat(sumProfit));
			conAmtBig.setCellValue(AmtUtil.change(sumProfit));
			totalRows++;

			Row rowlast_1_0 = sheet.createRow(totalRows);
			rowlast_1_0.setHeightInPoints(5);
			Cell con_0 = rowlast_1_0.createCell(0);
			con_0.setCellValue(" ");
			con_0.setCellStyle(infoStyle_left);
			dealCell(1, totalColums - 1, rowlast_1_0, infoStyle);
			con_0 = rowlast_1_0.createCell(totalColums - 1);
			con_0.setCellValue(" ");
			con_0.setCellStyle(infoStyle_right);
			totalRows++;

			// 倒数1行
			Row rowlast_2 = sheet.createRow(totalRows);
			// fillCell("国银通代理商综合分润明细表", 0, row0);
			rowlast_2.setHeightInPoints(110);
			Cell c_cons1 = rowlast_2.createCell(0);
			
			String line1 = "     如以上信息确认无误，请在收到此函五个工作日内盖章确认，并以传真或电子扫描件方式邮件回传至我司，\n 如逾期不予确认，当期平台结算款将延至次月进行结算。 \n             谢谢配合！ \n                                                       ";
			
			String dateStr = nowDate.substring(0, 4) + "  年   "
					+ nowDate.substring(4, 6) + "   月   "
					+ nowDate.substring(6, 8) + "   日      \n    ";
			c_cons1.setCellValue(line1+dateStr);
			c_cons1.setCellStyle(style1);
			dealCell(totalColums, rowlast_2, style1);
			sheet.addMergedRegion(new CellRangeAddress(totalRows, totalRows, 0,
					totalColums - 1));

			totalRows++;
			PrintSetup ps = sheet.getPrintSetup();
			// 设置自动换行
			sheet.setAutobreaks(true);

			ps.setFitHeight((short) 1);
			ps.setFitWidth((short) 1);

			// fileName = merchantAccount+"_" + cdate + ".xlsx";
			/*File fileDir = new File(remotePath);
			if (!fileDir.exists()) {
				fileDir.mkdirs();
			}
			String fileName = new String((merchant.getRealName() + "_" + year
					+ "年" + month + "月分润").getBytes())
					+ ".xlsx";
			FileOutputStream fout = new FileOutputStream(remotePath + "/"
					+ fileName);
			wb.write(fout);
			fout.flush();

			fout.close();
			logger.info("文件保存路径【" + remotePath + "/" + fileName + "】");*/

		} catch (Exception e) {
			logger.error("----------【" + merchant.getAccountNo()
					+ "】导出分润数据失败---------------");
			logger.error(e.getMessage(), e);
			throw new Exception("导出【" + merchant.getAccountNo() + "】的分润数据失败");

		}
		return wb;

	}

	private void fillCell(Object value, int i, Row row) {
		Cell c = row.createCell(i);
		c.setCellValue(ObjectUtils.toString(value));

		c = null;
	}

	public static CellStyle initStyle(SXSSFWorkbook wb, short alignType,
			String fontName, int fontSize) {
		CellStyle style = wb.createCellStyle();
		style.setAlignment(alignType);

		Font font = wb.createFont();
		font.setFontHeightInPoints((short) fontSize);
		// font.setUnderline(Font.U_SINGLE);
		font.setFontName(fontName);
		style.setFont(font);
		style.setFillForegroundColor((short) 9);// 设置背景色
		style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		style.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框
		style.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
		style.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框
		style.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框

		return style;
	}

	public static CellStyle initStyle_uline(SXSSFWorkbook wb, short alignType,
			String fontName, int fontSize) {
		CellStyle style = wb.createCellStyle();
		style.setAlignment(alignType);
		Font font = wb.createFont();
		font.setFontHeightInPoints((short) fontSize);
		// font.setUnderline(Font.U_SINGLE);
		font.setFontName(fontName);
		style.setFont(font);
		style.setFillForegroundColor((short) 9);// 设置背景色
		style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		style.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框
		style.setBorderLeft(HSSFCellStyle.BORDER_NONE);// 左边框
		style.setBorderTop(HSSFCellStyle.BORDER_NONE);// 上边框
		style.setBorderRight(HSSFCellStyle.BORDER_NONE);// 右边框

		return style;
	}

	public static CellStyle initStyle_right_u(SXSSFWorkbook wb,
			short alignType, String fontName, int fontSize) {
		CellStyle style = wb.createCellStyle();
		style.setAlignment(alignType);
		Font font = wb.createFont();
		font.setFontHeightInPoints((short) fontSize);
		// font.setUnderline(Font.U_SINGLE);
		font.setFontName(fontName);
		style.setFont(font);
		style.setFillForegroundColor((short) 9);// 设置背景色
		style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		style.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框
		style.setBorderLeft(HSSFCellStyle.BORDER_NONE);// 左边框
		style.setBorderTop(HSSFCellStyle.BORDER_NONE);// 上边框
		style.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框

		return style;
	}

	public static CellStyle initStyle_nobord(SXSSFWorkbook wb, short alignType,
			String fontName, int fontSize) {
		CellStyle style = wb.createCellStyle();
		style.setAlignment(alignType);
		Font font = wb.createFont();
		font.setFontHeightInPoints((short) fontSize);
		// font.setUnderline(Font.U_SINGLE);
		font.setFontName(fontName);
		style.setFont(font);
		style.setFillForegroundColor((short) 9);// 设置背景色
		style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		style.setBorderBottom(HSSFCellStyle.BORDER_NONE); // 下边框
		style.setBorderLeft(HSSFCellStyle.BORDER_NONE);// 左边框
		style.setBorderTop(HSSFCellStyle.BORDER_NONE);// 上边框
		style.setBorderRight(HSSFCellStyle.BORDER_NONE);// 右边框

		return style;
	}

	public static CellStyle initStyle_left(SXSSFWorkbook wb, short alignType,
			String fontName, int fontSize) {
		CellStyle style = wb.createCellStyle();
		style.setAlignment(alignType);
		Font font = wb.createFont();
		font.setFontHeightInPoints((short) fontSize);
		// font.setUnderline(Font.U_SINGLE);
		font.setFontName(fontName);
		style.setFont(font);
		style.setFillForegroundColor((short) 9);// 设置背景色
		style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		style.setBorderBottom(HSSFCellStyle.BORDER_NONE); // 下边框
		style.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
		style.setBorderTop(HSSFCellStyle.BORDER_NONE);// 上边框
		style.setBorderRight(HSSFCellStyle.BORDER_NONE);// 右边框

		return style;
	}

	public static CellStyle initStyle_right(SXSSFWorkbook wb, short alignType,
			String fontName, int fontSize) {
		CellStyle style = wb.createCellStyle();
		style.setAlignment(alignType);
		Font font = wb.createFont();
		font.setFontHeightInPoints((short) fontSize);
		// font.setUnderline(Font.U_SINGLE);
		font.setFontName(fontName);
		style.setFont(font);
		style.setFillForegroundColor((short) 9);// 设置背景色
		style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		style.setBorderBottom(HSSFCellStyle.BORDER_NONE); // 下边框
		style.setBorderLeft(HSSFCellStyle.BORDER_NONE);// 左边框
		style.setBorderTop(HSSFCellStyle.BORDER_NONE);// 上边框
		style.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框

		return style;
	}

	private static void dealCell(int cellNum, Row row, CellStyle style) {
		for (int i = 1; i < cellNum; i++) {
			Cell cTitle = row.createCell(i);
			cTitle.setCellValue("");
			cTitle.setCellStyle(style);
		}
	}

	public static void dealCell(int start, int cellNum, Row row, CellStyle style) {
		for (int i = start; i < cellNum; i++) {
			Cell cTitle = row.createCell(i);
			cTitle.setCellValue("");
			cTitle.setCellStyle(style);
		}
	}

	public static String format(Object o, Class c) {
		String result = "";
		if (o == null) {
			result = "";
		} else if (Double.class.equals(c)) {
			result = doubleFormat.format(o);
		} else if (Integer.class.equals(c)) {
			result = integerFormat.format(o);
		} else {
			result = ObjectUtils.toString(o);
		}
		return result;
	}

	/**
	 * 
	 * @param wb
	 *            Excel HSSFWorkbook 对象
	 * @param size
	 *            字体大小
	 * @param isCommon
	 *            true 普通文本 false 首行或标题
	 * @param bgColorNeeded
	 *            true 需要背景色 false 不需要
	 * @return HSSFCellStyle实例
	 */
	public static HSSFCellStyle initCellStyle(HSSFWorkbook wb, int size,
			boolean isCommon, boolean bgColorNeeded) {
		if (wb == null) {
			wb = new HSSFWorkbook();
		}
		HSSFCellStyle cellStyle = wb.createCellStyle();
		if (cellStyle == null) {
			return cellStyle;
		}

		HSSFFont cell_font = wb.createFont();
		if (cell_font == null) {
			return null;
		}
		if (isCommon == false) {
			cell_font.setFontHeightInPoints((short) size);// 字号
			cell_font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 加粗
			cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直
			cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 居中
			if (bgColorNeeded) {
				cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);// 背景颜色-淡灰
				cellStyle
						.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);// 背景颜色-淡灰
			}
			cellStyle.setFont(cell_font);
		} else {
			cellStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);// youbian
		}

		cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);// 下边框
		cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
		cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框
		cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框
		return cellStyle;

	}
	/**
	 * 
	 * @param wb
	 *            Excel HSSFWorkbook 对象
	 * @param size
	 *            字体大小
	 * @param isCommon
	 *            true 普通文本 false 首行或标题
	 * @param bgColorNeeded
	 *            true 需要背景色 false 不需要
	 * @return HSSFCellStyle实例
	 */
	public static HSSFCellStyle initCellStyle1(HSSFWorkbook wb, int size,
			boolean isCommon, boolean bgColorNeeded) {
		if (wb == null) {
			wb = new HSSFWorkbook();
		}
		HSSFCellStyle cellStyle = wb.createCellStyle();
		if (cellStyle == null) {
			return cellStyle;
		}

		HSSFFont cell_font = wb.createFont();
		if (cell_font == null) {
			return null;
		}
		if (isCommon == false) {
			cell_font.setFontHeightInPoints((short) size);// 字号
			cell_font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 加粗
			cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直
			cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 居中
			if (bgColorNeeded) {
				cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);// 背景颜色-淡灰
				cellStyle
						.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);// 背景颜色-淡灰
			}
			cellStyle.setFont(cell_font);
		} else {
			cellStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);// youbian
		}

		cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);// 下边框
		cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
		cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框
		cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框
		return cellStyle;

	}
	public static HSSFRow createRow(HSSFSheet sheet, int row_num,
			String[] coloumValue, HSSFCellStyle cellStyle, boolean widthFlag) {
		if (sheet == null)
			return null;
		if (coloumValue == null || coloumValue.length == 0)
			return null;
		HSSFRow row = sheet.createRow(row_num);
		if (row == null)
			return row;
		HSSFCell cell = null;
		if (widthFlag) {
			for (int i = 0; i < coloumValue.length; i++) {
				if (i == 1 || i == 6) {
					cell = row.createCell((short) i);
					cell.setCellStyle(cellStyle);
					sheet.setColumnWidth((short) 1, (short) 6000);
					cell.setCellValue(coloumValue[i]);
				} else {
					cell = row.createCell((short) i);
					cell.setCellStyle(cellStyle);
					sheet.setColumnWidth((short) i, (short) 4000);
					cell.setCellValue(coloumValue[i]);
				}
			}
		} else {
			for (int i = 0; i < coloumValue.length; i++) {
				cell = row.createCell((short) i);
				cell.setCellStyle(cellStyle);
				cell.setCellValue(coloumValue[i]);
			}
		}
		return row;
	}

	public static HSSFRow createRow1(HSSFSheet sheet, int row_num,
			String[] coloumValue, HSSFCellStyle cellStyle, boolean widthFlag) {
		if (sheet == null)
			return null;
		if (coloumValue == null || coloumValue.length == 0)
			return null;
		HSSFRow row = sheet.createRow(row_num);
		if (row == null)
			return row;
		HSSFCell cell = null;
		if (widthFlag) {
			for (int i = 0; i < coloumValue.length; i++) {
				if (i == 1 || i == 6) {
					cell = row.createCell((short) i);
					cell.setCellStyle(cellStyle);
					sheet.setColumnWidth((short) 1, (short) 6000);
					cell.setCellValue(coloumValue[i]);
				} else {
					cell = row.createCell((short) i);
					cell.setCellStyle(cellStyle);
					sheet.setColumnWidth((short) i, (short) 4000);
					cell.setCellValue(coloumValue[i]);
				}
			}
		} else {
			for (int i = 0; i < coloumValue.length; i++) {
				
//				if (i == 2 || i == 9) {
//					cell = row.createCell((short) i);
//					cell.setCellStyle(cellStyle);
//					cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
//					cell.setCellValue(Double.valueOf(coloumValue[i]));
//				}else{
					cell = row.createCell((short) i);
					cell.setCellStyle(cellStyle);
					cell.setCellValue(coloumValue[i]);
			//	}
				
			}
		}
		return row;
	}
	/**
	 * 拼接字符串
	 * 
	 * @param str
	 * @param addStr
	 * @param flag
	 * @param length
	 * @return
	 */
	public static String addStr(String str, String addStr, int flag, int length) {

		StringBuffer sb = new StringBuffer();
		for (int i = 0; i <= length; i++) {
			sb.append(addStr);
		}
		if (flag == 0) {
			str = str + sb.toString();
		} else {
			str = sb.toString() + str;
		}
		return str;
	}
	
	/**
	 * 
	 * @param wb
	 *            Excel SXSSFWorkbook 对象
	 * @param size
	 *            字体大小
	 * @param isCommon
	 *            true 普通文本 false 首行或标题
	 * @param bgColorNeeded
	 *            true 需要背景色 false 不需要
	 * @return SXSSFCellStyle实例
	 */
	public static CellStyle initCellStyle(SXSSFWorkbook wb, int size,
			boolean isCommon, boolean bgColorNeeded) {
		if (wb == null) {
			wb = new SXSSFWorkbook();
		}
		CellStyle cellStyle = wb.createCellStyle();
		if (cellStyle == null) {
			return cellStyle;
		}

		Font cell_font = wb.createFont();
		if (cell_font == null) {
			return null;
		}
		if (isCommon == false) {
			cell_font.setFontHeightInPoints((short) size);// 字号
			cell_font.setBoldweight(Font.BOLDWEIGHT_BOLD);// 加粗
			cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// 垂直
			cellStyle.setAlignment(CellStyle.ALIGN_CENTER);// 居中
			if (bgColorNeeded) {
				cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);// 背景颜色-淡灰
				cellStyle
						.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);// 背景颜色-淡灰
			}
			cellStyle.setFont(cell_font);
		} else {
			cellStyle.setAlignment(CellStyle.ALIGN_RIGHT);// youbian
		}

		cellStyle.setBorderBottom(CellStyle.BORDER_THIN);// 下边框
		cellStyle.setBorderLeft(CellStyle.BORDER_THIN);// 左边框
		cellStyle.setBorderRight(CellStyle.BORDER_THIN);// 右边框
		cellStyle.setBorderTop(CellStyle.BORDER_THIN);// 上边框
		return cellStyle;

	}
	
	public static Row createRow(SXSSFSheet sheet, int row_num,
			String[] coloumValue, CellStyle cellStyle, boolean widthFlag) {
		if (sheet == null)
			return null;
		if (coloumValue == null || coloumValue.length == 0)
			return null;
		Row row = sheet.createRow(row_num);
		if (row == null)
			return row;
		Cell cell = null;
		if (widthFlag) {
			for (int i = 0; i < coloumValue.length; i++) {
				if (i == 1 || i == 6) {
					cell = row.createCell((short) i);
					cell.setCellStyle(cellStyle);
					sheet.setColumnWidth((short) 1, (short) 6000);
					cell.setCellValue(coloumValue[i]);
				} else {
					cell = row.createCell((short) i);
					cell.setCellStyle(cellStyle);
					sheet.setColumnWidth((short) i, (short) 4000);
					cell.setCellValue(coloumValue[i]);
				}
			}
		} else {
			for (int i = 0; i < coloumValue.length; i++) {
				cell = row.createCell((short) i);
				cell.setCellStyle(cellStyle);
				cell.setCellValue(coloumValue[i]);
			}
		}
		return row;
	}
	
	
	 /**
     * 读入Excel文件，解析单元格内容，并存入二维数组
     * @param fileInputStream
     * @return String类型的二维数组
     * @throws ExcelException 
     */
    public List<List<String>> readExcel(FileInputStream fileInputStream) throws Exception {
        //声明并初始化一个工作薄对象
        Workbook wb = null;
        //存储解析后的Excel中的内容的二维数组
        List<List<String>> list = new ArrayList<List<String>>();

        try {
            //生成Excel工作薄对象
            wb = WorkbookFactory.create(fileInputStream);
            //获得工作薄中的第一个工作表
            Sheet sheet = wb.getSheetAt(0);
            //调用readRows方法，逐行解析工作表中内容
            list = readRows(sheet); 
            //解析Excel文件结束后，关闭输入流
            fileInputStream.close();
        }catch(Exception e){
            throw (new Exception("导入失败，请重新尝试！"));
        } 
        return list;
    }

    /**
     * 逐行解析工作表中的内容
     * @param sheet
     * @return 解析工作表后生成的二维数组
     * @throws Exception
     */
    private List<List<String>> readRows(Sheet sheet) {

        //声明并初始化String类型的二维数组，用于存储解析后的工作表中的内容
        List<List<String>> list= new ArrayList<List<String>>();

        //获得该工作表的行数
        int rows = sheet.getPhysicalNumberOfRows();
        int rowIndex = 0;        //每行索引
        int notnullRowIndex = 0; //非空行索引
        while (notnullRowIndex < rows) {
            //解析该行中的每个单元格
            Row row = sheet.getRow(rowIndex);
            rowIndex++;
            if (row != null) {
                //将每行解析出的一维数组加入到list中
                list.add(readCells(row));
                notnullRowIndex++;
            }
        }
        return list;
    }   

    /**
     * 解析工作表中的一行数据
     * @param row
     * @return 本行解析后的一维数组
     * @throws Exception
     */
    private ArrayList<String> readCells(Row row) {

        //获得本行所有单元格
        int cells  = row.getPhysicalNumberOfCells();
        int cellIndex = 0;         //单元格索引
        int notnullCellIndex = 0;  //非空单元格索引

        //声明并初始化一个一维数组，用于存储改行解析后的数据
        ArrayList<String> rowlist = new ArrayList<String>();

        //逐个解析该行的单元格
        while(notnullCellIndex < cells) {

            //取得一个单元格
            Cell cell = row.getCell(cellIndex);
            

            cellIndex++;
            if (cell != null) {
                String value = null;

                //根据单元格内容的类型分别处理
                switch (cell.getCellType()) {                                   
                case Cell.CELL_TYPE_FORMULA:   
                    value = cell.getCellFormula();   
                    break; 
                case Cell.CELL_TYPE_NUMERIC:{

                    //若该单元格内容为数值类型，则判断是否为日期类型
                    if(cell.getCellStyle().getDataFormatString().equals("yyyy\"年\"m\"月\";@") || HSSFDateUtil.isCellDateFormatted(cell)){                            
                        try{
                            //若转换发生异常，则记录下单元格的位置，并跳过该行的读取
                            value = String.valueOf(DateUtil.Date2String(cell.getDateCellValue(), "yyyy-MM-dd"));
                        }catch(Exception e){
                            errorMessage.append(row.getRowNum()+"行"+notnullCellIndex+"列"+"日期格式错误;");
                            return null;
                        }

                    }else{
                        try {
                            //将数字类型转换为字符串类型，若为浮点类型数据，则去除掉小数点后的内容
                            value = String.valueOf(cell.getNumericCellValue());
                        }catch(Exception e){
                            //若转换发生异常，则记录下单元格的位置，并跳过该行的读取
                            errorMessage.append(row.getRowNum()+"行"+notnullCellIndex+"列"+"数字格式错误;");
                            return null;
                        }
                    }
                    break; 
                }
                case Cell.CELL_TYPE_STRING:   
                    value = cell.getStringCellValue();   
                    break;                
                case Cell.CELL_TYPE_BLANK:   
                    value = "";                            
                    break;
                default: {
                    //若单元格内容的格式与常规类型不符，则记录下单元格位置，并跳过该行的读取，继续解析下一行
                    errorMessage.append(row.getRowNum()+"行"+notnullCellIndex+"列"+"格式与常规类型不符;");
                    return null;
                }  
                }   
                notnullCellIndex++;
                //将该单元格的内容存入一维数组
                rowlist.add(value); 
            }
        } 
        return rowlist;
    }

    
    /**
     * 根据传入的list和文件名生成Excel文件
     * 保存到target/temp文件夹中，下载完毕后删除临时文件
     * @param list
     */
    public XSSFWorkbook writeExcel(ArrayList<ArrayList<String>> list){
        
        //实例化工作薄对象
        XSSFWorkbook wb = new XSSFWorkbook();
        //创建一个工作表
        XSSFSheet sheet = wb.createSheet();
        //设置单元格的格式
        XSSFCellStyle style = wb.createCellStyle();
        style.setFillPattern(XSSFCellStyle.NO_FILL);
        
        for(int i = 0;i < list.size();i++){
            //创建一行
            XSSFRow row = sheet.createRow(i);
            
            ArrayList<String> rowlist = list.get(i);
            for(int j = 0;j < rowlist.size();j++){
                //创建一个单元格，并根据数组中的内容填充该单元格
                XSSFCell cell = row.createCell(j);
                cell.setCellType(Cell.CELL_TYPE_STRING);
                cell.setCellStyle(style);
                cell.setCellValue(rowlist.get(j));
            }
        }
        
        return wb;
    }
    /**
     * 根据传入的list和文件名生成Excel文件
     * 保存到target/temp文件夹中，下载完毕后删除临时文件
     * 并设置数据的有效性
     * 崔
     * @param list
     */
    public HSSFWorkbook writeExcel(ArrayList<ArrayList<String>> list,HSSFDataValidation dataValidation){
        // 取得规则  
        //实例化工作薄对象
        HSSFWorkbook wb = new HSSFWorkbook();  
        //创建一个工作表
        HSSFSheet sheet = wb.createSheet();  
        //设置单元格的格式
        sheet.addValidationData(dataValidation);
        HSSFCellStyle style = wb.createCellStyle();
        style.setFillPattern(HSSFCellStyle.NO_FILL);
        
        for(int i = 0;i < list.size();i++){
            //创建一行
            HSSFRow row = sheet.createRow(i);
            
            ArrayList<String> rowlist = list.get(i);
            for(int j = 0;j < rowlist.size();j++){
                //创建一个单元格，并根据数组中的内容填充该单元格
                HSSFCell cell = row.createCell(j);
                cell.setCellType(Cell.CELL_TYPE_STRING);
                cell.setCellStyle(style);
                cell.setCellValue(rowlist.get(j));
            }
        }
        
        return wb;
    }

	public static StringBuffer getErrorMessage() {
		return errorMessage;
	}

	public static void setErrorMessage(StringBuffer errorMessage) {
		ExcelUtil.errorMessage = errorMessage;
	}
    
}
